import sqlite3
import re
from sched_db.querybuilder import *

def import_banner(dbfile, semester, feed):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  for t in feed:
    # data
    code = "%s %s" % (t['subject'], t['course'])
    try:
      bmin = int(btime[:2]) * 60 + int(btime[2:])
      emin = int(etime[:2]) * 60 + int(etime[2:])
      duration = emin - bmin
    except:
      duration = None

    # import course
    course = (code, t['sectiontitle'])
    c.execute("INSERT OR REPLACE INTO Course VALUES (?, ?)", course)
    # import section
    section = (t['sec'],t['crn'],code, semester,
               t['max'],t['act'],duration,t['del'])
    c.execute("INSERT OR REPLACE INTO Section VALUES (?,?,?,?,?,?,?,?)",
              section)
    #import schedule
    schedule = (t['sec'],code, semester, t['schd'], t['campus'],
                t['room'],t['day'],t['btime'],t['etime'], None)
    c.execute("INSERT OR REPLACE INTO Schedule VALUES (?,?,?,?,?,?,?,?,?,?)",
              schedule)
  db.commit()
  db.close()

def import_conflicts(dbfile, semester, feed):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  for t in feed:
    sem = t['course'][:3]
    code = re.sub(r'\s+', ' ', t['course'][3:])
    if sem.startswith("F"):
      semester = "20" + sem[1:] + "09"
    elif sem.startswith("W"):
      semester = "20" + sem[1:] + "01"
    else:
      print "Warning: semester is %s" % sem
      semester = sem

    conflict = (t['dept'], t['program'], t['programdescription'], t['level'],
                semester, code, t['coursedescription'], t['category'],
                t['catdescription'])
    p = ",".join("?" for x in conflict)
    c.execute("insert or replace into Conflict values(%s)" % p, conflict)

  db.commit()
  db.close()

def import_locations(dbfile, semester, feed):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  for t in feed:
    c.execute('INSERT OR REPLACE INTO Room VALUES (?,?,?, ?)',
              (t['campus'], t['location'], t['capacity'], t['remark']))
  c.close()
  db.commit()
  db.close()

def build_starttimes(dbfile):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  for h in range(8, 21):
    for m in [0, 30]:
      btime = "%.2d%.2d" % (h, m)
      for day in 'mtwrf'.upper():
        c.execute('INSERT OR REPLACE INTO StartTime VALUES (?, ?)', 
                 (btime, day))
  db.commit()
  db.close()

def build_freerooms(dbfile, semester):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  c.execute("DELETE FROM FreeRoom")
  for dur in [30]: # We are only building free rooms for 30 intervals
    print "Free rooms for %d minutes" % dur
    sql = """
    INSERT INTO FreeRoom
      SELECT '%s', R.campus, R.location, R.capacity, T.btime, %s, T.day, %d
      FROM StartTime T, Room R
      WHERE NOT EXISTS (
        SELECT * FROM Schedule S
        WHERE S.semester = ?
        AND S.day = T.day
        AND S.location = R.location
        AND %s
    )""" % (semester, conv_to_minutes('T.btime'), dur, is_overlapping(
      conv_to_minutes('T.btime'), conv_to_minutes('T.btime', dur),
      conv_to_minutes('S.btime'), conv_to_minutes('S.etime')))
    print "SQL--------\n", sql
    c.execute(sql, (semester,))
    c.execute("""
    create index FreeRoom_Idx on FreeRoom(semester, day, location, btimemin)
    """)
  db.commit()
  db.close()

def build_conflictview(dbfile, semester):
  db = sqlite3.connect(dbfile)
  c = db.cursor()
  c.execute("drop table if exists ConflictSchedule")
  c.execute("""
    create table ConflictSchedule as
    select * from Conflict natural join Schedule
    """)
  c.execute("""
    create index ConflictSchedule_Idx on ConflictSchedule (
      dept, program, level, day)
    """)
  db.commit()
  db.close()
